<?php
/**
 * Created by PhpStorm.
 * User: Digvijay
 * Date: 3/23/14
 * Time: 4:27 PM
 */

class Reportgenerator extends CI_Controller {
    public static $em;

    function __construct() {
        parent::__construct();

        $this->em = $this->doctrine->em;
        date_default_timezone_set('Asia/Calcutta');
    }

    function index() {
        $this->load->library('excel');
        $this->excel->setActiveSheetIndex(0);
        //name the worksheet
        $this->excel->getActiveSheet()->setTitle('test worksheet');
        //set cell A1 content with some text
        $this->excel->getActiveSheet()->setCellValue('A1', 'This is just some text value');
        //change the font size
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        //make the font become bold
        $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //merge cell A1 until D1
        $this->excel->getActiveSheet()->mergeCells('A1:D1');
        //set aligment to center for that merged cell (A1 to D1)
        $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $filename = 'just_some_random_name.xlsx';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
        $objWriter->save('php://output');
    }

    function generatespstock($startDate = null, $endDate = null) {
        try {
            date_default_timezone_set('Asia/Calcutta');
            if ($startDate == null || $endDate == null) {
                $endDate = new DateTime();
                $endDate = $endDate->format("d-m-Y");
                $startDate = new DateTime();
                $startDate = "1-" . $startDate->format("m-Y");
            }
            $cols = array("A" => "Sr.No", "B" => "Date", "C" => "Grade", "D" => "Opening Stock (Daily)", "E" => "(Add) Purchase & Production", "F" => "(Less) Sales & Consumption", "G" => "Closing Stock");
            $this->load->library('excel');
            $this->excel->setActiveSheetIndex(0);
            $this->excel->getActiveSheet()->setTitle("Solid Petroleum-Stock");
            $this->excel->getActiveSheet()->setCellValue('A1', 'Solid Petroleum-Stock');
            $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
            $this->excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $this->excel->getActiveSheet()->mergeCells('A1:G1');
            $this->excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            foreach ($cols as $col => $header) {
                $excelColumn = $col . "2";
                $this->excel->getActiveSheet()->setCellValue($excelColumn, $header);
                $this->excel->getActiveSheet()->getStyle($excelColumn)->getFont()->setSize(12);
                $this->excel->getActiveSheet()->getStyle($excelColumn)->getFont()->setBold(true);
                $this->excel->getActiveSheet()->getStyle($excelColumn)->getFill()->applyFromArray(array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => "FFFF00")));
                $this->excel->getActiveSheet()->getStyle($excelColumn)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
            }

            $spstocks = $this->getDataFor("Solidpetrostock", $startDate, $endDate, "ASC");
            $colIndex = 2;
            $srno = 0;
            foreach ($spstocks as $stock) {
                $dateStr = $stock->getDate()->format('d-m-Y');
                $data = array("A" => ++$srno, "B" => $dateStr, "C" => $stock->getGrade()->getName(), "D" => $stock->getOpeningStock(), "E" => $stock->getPurchaseAndProduction(), "F" => $stock->getSaleAndConsumption(), "G" => $stock->getClosingStock());
                $colIndex = $colIndex + 1;
                foreach ($data as $col => $value) {
                    $col = $col . $colIndex;
                    $this->excel->getActiveSheet()->setCellValue($col, $value);
                }
            }
            $filename = "SolidPetro-Stock-" . $startDate . "To" . $endDate . ".xlsx";
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            header('Set-Cookie: fileDownload=true; path=/');
            $objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
            $objWriter->save('php://output');
        } catch (Exception $e) {
            echo $e->getMessage();
        }

    }

    function getDataFor($object, $from, $to, $orderBy) {
        $from = new DateTime($from);
        $to = new DateTime($to);
        $query = $this->em->createQuery("SELECT u FROM " . $object . " u WHERE u.date >= :from AND u.date <= :to ORDER BY u.date " . $orderBy.", u.id ". $orderBy);
        $query->setParameter("from", $from);
        $query->setParameter("to", $to);
        return $query->getResult();
    }
} 